/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao.impl;

import dao.intf.DAO;
import database.ConnectionUtil;
import database.UtilDB;
import entities.GroupVO;
import entities.VO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.logging.Level;
import org.apache.log4j.Logger;
import utils.LogUtils;

/**
 *
 * @author Valentina
 */
public class GroupDAOImpl implements DAO {
    
    Logger logger = LogUtils.getLogger(this.getClass().getName());
    
    private final String SQL_SAVE = "INSERT INTO pentalab.GROUP VALUES (NULL,?,?)";
    private final String SQL_LAST_ID = "SELECT MAX(IDGROUP) FROM pentalab.GROUP";
    
    private final String SQL_UPDATE = "UPDATE pentalab.GROUP SET NAME=?, DESCRIPTION=? WHERE IDGROUP=?";
    
    private final String SQL_DELETE = "DELETE FROM pentalab.group WHERE IDGROUP=?";
    
    private final String SQL_FIND_BY_ID = "SELECT * FROM pentalab.GROUP WHERE IDGROUP=?";
    private final String SQL_FIND_ALL = "SELECT * FROM pentalab.GROUP";
   

    @Override
    public Long create(VO valueObject) {
        
        GroupVO groupVo = (GroupVO) valueObject;
        Connection con = ConnectionUtil.getConnection();
        Long maxId = 0L;
        
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            con.setAutoCommit(false);

            stmt = con.prepareStatement(SQL_SAVE);

            stmt.setString(1, groupVo.getName());
            stmt.setString(2, groupVo.getDescription());

            stmt.executeUpdate();

            stmt = con.prepareStatement(SQL_LAST_ID);
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                maxId = rs.getLong(1);
            }

            con.commit();
        } catch (SQLException e) {

            maxId = 0L;

            logger.error(e.getNextException().getMessage());
            
            try {
                con.rollback();
            } catch (SQLException ex) {
                logger.error(ex.getNextException().getMessage());
            }
        }finally{
            UtilDB.closeConnection(con);
            try {
                stmt.close();
                rs.close();
            } catch (SQLException ex) {
                java.util.logging.Logger.getLogger(GroupDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        return maxId;
    }

    @Override
    public Boolean update(VO valueObject) {
        
        GroupVO groupVo = (GroupVO) valueObject;
        Connection con = ConnectionUtil.getConnection();
        PreparedStatement stmt = null;
        
        try {
            
            stmt = con.prepareStatement(SQL_UPDATE);

            stmt.setString(1, groupVo.getName());
            stmt.setString(2, groupVo.getDescription());
            stmt.setLong(3, groupVo.getId());

            stmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            logger.error(e.getNextException().getMessage());
            return false;
        }finally{
            UtilDB.closeConnection(con);
            try {
                stmt.close();
            } catch (SQLException ex) {
                java.util.logging.Logger.getLogger(GroupDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
    }

    @Override
    public Boolean delete(Long id) {
        Connection con = ConnectionUtil.getConnection();
        PreparedStatement stmt = null;
        
        try {            
            stmt = con.prepareStatement(SQL_DELETE);
            stmt.setLong(1, id);
            stmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            logger.error(e.getNextException().getMessage());
            return false;
        }finally{
            UtilDB.closeConnection(con);
            try {
                stmt.close();
            } catch (SQLException ex) {
                java.util.logging.Logger.getLogger(GroupDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    @Override
    public Collection<? extends VO> retrieve() {
        Collection<GroupVO> coll = null;
        GroupVO valueObject = null;
        Connection con = ConnectionUtil.getConnection();
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            stmt = con.prepareStatement(SQL_FIND_ALL);
            rs = stmt.executeQuery();
            coll = new ArrayList<GroupVO>();

            while (rs.next()) {
                Long iddb = rs.getLong(1);
                String name = rs.getString(2);
                String description = rs.getString(3);

                valueObject = new GroupVO(iddb, name, description);
                coll.add(valueObject);
                
            }
            
        } catch (SQLException e) {
            logger.error(e.getNextException().getMessage());

        } finally {
            UtilDB.closeConnection(con);
            try {
                stmt.close();
                rs.close();
            } catch (SQLException ex) {
                java.util.logging.Logger.getLogger(GroupDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        return coll;
    }

    
    @Override
    public VO retrieve(Long id) {
        VO valueObject = null;
        Connection con = ConnectionUtil.getConnection();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            stmt = con.prepareStatement(SQL_FIND_BY_ID);
            stmt.setLong(1, id);

            rs = stmt.executeQuery();

            if (rs.next()) {
                Long iddb = rs.getLong(1);
                String name = rs.getString(2);
                String description = rs.getString(3);
                valueObject = new GroupVO(iddb, name, description);

            }
        } catch (SQLException e) {
            logger.error(e.getNextException().getMessage());
        }finally{
            UtilDB.closeConnection(con);
            try {
                stmt.close();
                rs.close();
            } catch (SQLException ex) {
                java.util.logging.Logger.getLogger(GroupDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return valueObject;
    }
}